package dbase;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import model.kendaraan;

/**
 *
 * @author aa
 */
public class DBkendaraan {
    Connection conn;

    public DBkendaraan(Connection conn){
        this.conn = conn;
    }

    public void insertKendaraan(String noPolisi, String noRangka, String noMesin, String noSTNK, String warna, String merek, String jenisMotor, String nrp) throws Exception {
        String sql = "INSERT INTO kendaraan (noPolisi, noRangka, noMesin, noSTNK, warna, merek, jenisMotor, nrp) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement st = conn.prepareStatement(sql);
        st.setString(1, noPolisi);
        st.setString(2, noRangka);
        st.setString(3, noMesin);
        st.setString(4, noSTNK);
        st.setString(5, warna);
        st.setString(6, merek);
        st.setString(7, jenisMotor);
        st.setString(8, nrp);
        st.executeUpdate();
        st.close();
    }

    public List<kendaraan> getAllKendaraan() throws Exception {
        List<kendaraan> kend = new ArrayList<kendaraan>();
        String sql = "SELECT * FROM kendaraan";
        PreparedStatement st = this.conn.prepareStatement(sql);
        ResultSet rs = st.executeQuery();
        while(rs.next()){
            kendaraan kendaraanData = new kendaraan();
            kendaraanData.setIdKendaraan(rs.getInt(1));
            kendaraanData.setNoPolisi(rs.getString(2));
            kendaraanData.setNoRangka(rs.getString(3));
            kendaraanData.setNoMesin(rs.getString(4));
            kendaraanData.setNoSTNK(rs.getString(5));
            kendaraanData.setWarna(rs.getString(6));
            kendaraanData.setMerek(rs.getString(7));
            kendaraanData.setJenisMotor(rs.getString(8));
            kend.add(kendaraanData);
        }
        return kend;
    }
    public kendaraan getKendaraan(int id) throws Exception {
        kendaraan kendaraan1 = null;
        String sql = "SELECT * FROM kendaraan WHERE id=?";
        PreparedStatement st = this.conn.prepareStatement(sql);
        st.setInt(1, id);
        ResultSet rs = st.executeQuery();
        if(rs.next()){
            kendaraan1 = new kendaraan();
            kendaraan1.setIdKendaraan(rs.getInt(1));
            kendaraan1.setNoPolisi(rs.getString(2));
            kendaraan1.setNoRangka(rs.getString(3));
            kendaraan1.setNoMesin(rs.getString(4));
            kendaraan1.setWarna(rs.getString(5));
            kendaraan1.setMerek(rs.getString(6));
            kendaraan1.setJenisMotor(rs.getString(7));
        }
        return kendaraan1;
    }

    public List<kendaraan> getKendaraanByCategory(String nrp) throws Exception {
        List<kendaraan> kend = new ArrayList<kendaraan>();
        String sql = "SELECT * FROM kendaraan WHERE nrp=?";
        PreparedStatement st = this.conn.prepareStatement(sql);
        st.setString(1, nrp);
        ResultSet rs = st.executeQuery();
        while(rs.next()){
            kendaraan  kendaraanData= new kendaraan();
            kendaraanData.setIdKendaraan(rs.getInt(1));
            kendaraanData.setNoPolisi(rs.getString(2));
            kendaraanData.setNoRangka(rs.getString(3));
            kendaraanData.setNoMesin(rs.getString(4));
            kendaraanData.setNoSTNK(rs.getString(5));
            kendaraanData.setWarna(rs.getString(6));
            kendaraanData.setMerek(rs.getString(7));
            kendaraanData.setJenisMotor(rs.getString(8));
            kendaraanData.setNrp(rs.getString(9));
            kend.add(kendaraanData);
        }
        return kend;
    }

    public void updateKendaraan (String noPolisi, String noRangka, String noMesin, String noSTNK, String warna, String merek, String jenisMotor, String nrp) throws Exception {
        String sql = "UPDATE kendaraan set noPolisi=?, noRangka=?, noMesin=?, noSTNK=?, warna=?, merek=?, jenisMotor=? WHERE nrp =?" ;
        try {
            PreparedStatement st = conn.prepareStatement(sql);
            st.setString(1, noPolisi);
            st.setString(2, noRangka);
            st.setString(3, noMesin);
            st.setString(4, noSTNK);
            st.setString(5, warna);
            st.setString(6, merek);
            st.setString(7, jenisMotor);
            st.setString(8, nrp);
            st.executeUpdate();
            st.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void deleteKendaraan (int idKendaraan){
        String sql = "DELETE from kendaraan WHERE idKendaraan = ?";
        try {
            PreparedStatement st = this.conn.prepareStatement(sql);
            st.setInt(1, idKendaraan);

            st.executeUpdate();
            st.close();
        } catch (Exception e) {
            e.printStackTrace ();
        }
    }

        }


